﻿'''
分数数据提交系统主文件
'''
# 导入系统模块
import os
import pyodbc
from datetime import datetime
from flask import Flask, render_template, redirect, url_for, request,session
from flask_wtf import FlaskForm
from wtforms import StringField, SubmitField, BooleanField, MultipleFileField
from wtforms.validators import DataRequired, Length
from flask_wtf.file import FileField, FileRequired, FileAllowed
from flask_csv import send_csv
import sqlalchemy

# 导入自制模块
from datatosql import data_to_sql, info_to_sql
from weblog import get_logger
from Pes import get_pes_data

# 初始化日志功能，启动日志记录
logger = get_logger(__name__)
# logger.info("....")

# Flask实例化，加密Cookie
app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'secret string')
# 删除Jinja2语句中的空格和制表符
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
# 上传附件最大10Mb上限
app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024
app.config['ALLOWED_EXTENSIONS'] = ['csv']

# 分光机设备IP地址列表，后续根据ip判定设备编号，192.168.3.229为服务器地址，其他计算机需要访问需要加上IP
ipLists = ['192.168.3.229', '192.168.30.195', '192.168.30.16', '192.168.30.76', \
           '192.168.30.151', '192.168.30.10', '192.168.30.66', '192.168.30.83', '192.168.30.196', '192.168.30.176',
           '192.168.20.7', '127.0.0.1', '192.168.30.180', '192.168.3.159', '192.168.30.169', '192.168.30.45', \
           '192.168.30.3', '192.168.30.179', '192.168.60.98', '192.168.20.219', '192.168.20.133', '192.168.30.108', \
           '192.168.30.72', '192.168.3.176', '192.168.20.47', '192.168.20.11']


# 定义工单下载输入界面表单类，设置相关验证要求
class MoForm(FlaskForm):
    mo_id = StringField('工单号', validators=[DataRequired(), Length(13)])
    submit = SubmitField('下载')


# 定义工单下载输入界面表单类，设置相关验证要求
class MixForm(FlaskForm):
    mo_id = StringField('工单号', validators=[DataRequired(), Length(13)])
    user_id = StringField('工号', validators=[DataRequired(), Length(5)])
    lot_id = StringField('批号', validators=[DataRequired()])
    excel_data = FileField('附件', validators=[FileRequired(), FileAllowed(['csv'])])
    submit = SubmitField('提交')


# 定义数据上传表单类，设置相关验证要求
class DataForm(FlaskForm):
    mo_id = StringField('工单号', validators=[DataRequired(), Length(13)])
    user_id = StringField('工号', validators=[DataRequired(), Length(5)])
    pes = BooleanField('小批量对测')
    excel_data = MultipleFileField('附件', validators=[DataRequired(), FileAllowed(['csv', 'xls'])])
    submit = SubmitField('提交')


# 定义文件修改函数，新文件名为 工单号+批号+扩展名
def alter_filename(filename, mo_id, lot_id):
    ext = os.path.splitext(filename)[1]
    new_filename = mo_id + '-' + str(lot_id) + ext
    return new_filename


# 注册/uploadData路由和uploadData视图函数
@app.route('/', methods=['GET', 'POST'])
def uploadData():
    # 数据库连接
    # cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31,1433;'
    #                       'DATABASE=SortingDB;UID=pereader;PWD=pereader')
    # cursor1 = cnxn.cursor()
    conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.3.236,1433;'
                          'DATABASE=SortingDB;UID=qatest;PWD=qatest')
    cursor2 = conn.cursor()
    form = DataForm()
    # 根据IP地址判定并添加MachineID，服务器ID为0，非分光机台为100
    ip = request.remote_addr
    try:
        machine_id = ipLists.index(ip)
    except ValueError:
        machine_id = 100
    # 非授权计算机IP将被重新定向到拒绝访问页面
    if machine_id == 100:
        return redirect(url_for('reject'))
    session['error'] = None
    session['data'] = None
    session['connect'] = None
    # 根据年月份自动创建上传文件目录
    uploadsDir = os.path.join('uploadData', str(datetime.now().year), str(datetime.now().month))
    if os.path.exists(uploadsDir):
        pass
    else:
        os.makedirs(uploadsDir)
    app.config['UPLOAD_PATH'] = os.path.join(app.root_path, uploadsDir)
    if form.validate_on_submit():
        error_list = []
        data_list = []
        connect_list = []
        # 获得文件列表
        file_list = request.files.getlist('excel_data')
        for file, i in zip(file_list, range(len(file_list))):
            # 获取上传文件信息
            f = file
            len1 = len(error_list)
            # 调用Pes/get_pes_data
            if form.pes.data:
                pes = '7'
            else:
                pes = get_pes_data(f.filename)
            # 调用infos_to_sql方法将表单基本信息写入数据库，返回lot_id
            cursor2.execute(
                f"SELECT MAX(LotID) FROM DP_test_data_info WHERE MoID='{form.mo_id.data}';")
            rows = cursor2.fetchall()
            if rows[0][0] is None:
                lot_id = 1
            else:
                lot_id = rows[0][0] + i + 1 - len1
            # 修改文件名，设置上传文件路径
            filename = alter_filename(f.filename, form.mo_id.data, lot_id)
            path = os.path.join(app.config['UPLOAD_PATH'], filename)
            # 根据路径保存文件
            f.save(path)
            try:
                if pes == '0':
                    error_list.append(f.filename)
                    continue
                # 调用csv_to_sql方法将数据写入数据库
                data_to_sql(csv_path=path, moID=form.mo_id.data, lotID=lot_id,
                            machine_id=machine_id, processID=pes, userID=form.user_id.data)
                info_to_sql(MoID=form.mo_id.data, MachineID=machine_id, UserID=form.user_id.data,
                            Procession=pes, UploadPath=os.path.join(uploadsDir, filename),
                            originalFileName=f.filename)
            except Exception as e:
                if 'DataError' in str(e):
                    data_list.append(f.filename)
                    logger.error(f"{form.mo_id.data}-上传{f.filename}错误！问题：{e}")
                elif '关闭了一个现有的连接' in str(e):
                    connect_list.append(f.filename)
                    logger.warn(f"{f.filename}-连接意外关闭")
                else:
                    logger.info(f"{form.mo_id.data}-{f.filename},文件上传成功")
        conn.close()
        if error_list:
            session['error'] = error_list
            session['data'] = data_list
            session['connect'] = connect_list
            return redirect(url_for('result'))
        else:
            return redirect(url_for('result'))
    return render_template('uploadData.html', form=form)


# 注册/result路由和result视图函数
@app.route('/result', methods=['GET'])
def result():
    list1 = session.get('error')
    list2 = session.get('data')
    list3 = session.get('connect')
    return render_template('result.html', list1=list1, list2=list2, list3=list3)


# 注册/reject路由和reject视图函数
@app.route('/reject', methods=['GET'])
def reject():
    return render_template('reject.html')


# 主程序入口
if __name__ == '__main__':
    app.run(debug=True)
